<?php

/**
 * @author Minh Luan
 * @copyright 2012
 */
 include_once("ConnectionManager.php");
//PRODUCT STATUS
//1: AVAILABLE
//2: OUT OF STOCK
//3 :DISABLE
//4: INCOMING
function getProductOfCategory($cat_id, $startRecord, $numberOfRecord, $keyword){
    if($cat_id > 0){
        $query = "select PRO_ID, CAT_ID, PRO_CODE, PRO_NAME_EN, PRO_NAME_VN, PRO_ADDEDDATE, PRO_PRICE, PRO_DESCRIPTION_EN, PRO_DESCRIPTION_VN, PRO_STATUS
        from GIAAN_PRODUCT
        where CAT_ID = $cat_id and (PRO_NAME_EN like '%$keyword%' or PRO_NAME_VN like '%$keyword%' or PRO_CODE like '%$keyword%') 
        limit $startRecord, $numberOfRecord";    
    }else{
        $query = "select PRO_ID, CAT_ID, PRO_CODE, PRO_NAME_EN, PRO_NAME_VN, PRO_ADDEDDATE, PRO_PRICE, PRO_DESCRIPTION_EN, PRO_DESCRIPTION_VN, PRO_STATUS
        from GIAAN_PRODUCT
        where PRO_NAME_EN like '%$keyword%' or PRO_NAME_VN like '%$keyword%' or PRO_CODE like '%$keyword%'
        limit $startRecord, $numberOfRecord";
    }
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function countTopSellingProducts() {
	$query = "SELECT PRO_ID, SUM(REI_QUANTITY) as SUM FROM GIAAN_RECEIPTITEM GROUP BY PRO_ID ORDER BY SUM DESC LIMIT 0, 18";
	ConnectionManager::getInstance()->setQuery($query);
	return mysql_num_rows(ConnectionManager::getInstance()->query());	
}

function getTopSellingProducts($startRecord, $numberOfRecord) {
	$query = "SELECT GIAAN_PRODUCT.PRO_ID, CAT_ID, PRO_CODE, PRO_NAME_EN, PRO_NAME_VN, PRO_PRICE, PRO_DESCRIPTION_EN, PRO_DESCRIPTION_VN, PRO_STATUS, SUM(REI_QUANTITY) as SUM 
			  FROM GIAAN_RECEIPTITEM JOIN GIAAN_PRODUCT ON GIAAN_RECEIPTITEM.PRO_ID = GIAAN_PRODUCT.PRO_ID
			  GROUP BY GIAAN_PRODUCT.PRO_ID 
			  ORDER BY SUM DESC 
			  LIMIT $startRecord, $numberOfRecord";
	ConnectionManager::getInstance()->setQuery($query);
	return ConnectionManager::getInstance()->query();
}

function getProduct($pro_id){
    $query = "select PRO_ID, CAT_ID, PRO_CODE, PRO_NAME_EN, PRO_NAME_VN, PRO_ADDEDDATE, PRO_PRICE, PRO_DESCRIPTION_EN, PRO_DESCRIPTION_VN, PRO_STATUS
        from GIAAN_PRODUCT
        where PRO_ID = $pro_id";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function getCategoryOfProduct($pro_id){
    $query = "select CAT_ID, CAT_NAME_EN, CAT_NAME_VN, CAT_DESCRIPTION_EN, CAT_DESCRIPTION_VN, CAT_PARENT_ID, CAT_IMG_URL
            from GIAAN_CATEGORY
            where CAT_ID = (select CAT_ID from GIAAN_PRODUCT where PRO_ID = $pro_id)";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function getImageOfProduct($pro_id){
    $query = "select IMG_ID, PRO_ID, IMG_PATH, IMG_DESCRIPTION
            from GIAAN_IMAGE
            where PRO_ID = $pro_id
            order by IMG_ID";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function getImageInformation($img_id){
    $query = "select IMG_ID, PRO_ID, IMG_PATH, IMG_DESCRIPTION
            from GIAAN_IMAGE
            where IMG_ID = $img_id";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function addProduct($pro_name_en, $pro_name_vn, $pro_code, $description_en, $description_vn, $pro_price, $cat_parent_id, $pro_status){
    $today = date("y-m-d");
    $query = "insert into GIAAN_PRODUCT(PRO_NAME_EN, PRO_NAME_VN, PRO_CODE, PRO_DESCRIPTION_EN, PRO_DESCRIPTION_VN, PRO_PRICE, CAT_ID, PRO_STATUS, PRO_ADDEDDATE)
            values('$pro_name_en','$pro_name_vn','$pro_code','$description_en','$description_vn',$pro_price,$cat_parent_id,$pro_status,'$today')";
	ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
}

function addImageOfProduct($pro_id, $img_path, $img_description){
    $query = "insert into GIAAN_IMAGE(PRO_ID, IMG_PATH, IMG_DESCRIPTION)
                values($pro_id, '$img_path', '$img_description')";
    ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
}

function deleteProductInformation($pro_id){
    $query = "delete from GIAAN_PRODUCT where PRO_ID = $pro_id";
    ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
    if(mysql_affected_rows()>0){
        return true;
    }else{
        return false;
    }
}

function deleteImageUrl($img_id){
    $query = "delete from GIAAN_IMAGE where IMG_ID = $img_id";
    ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
}

function updateImageUrl($img_id, $url){
    $query = "update GIAAN_IMAGE set IMG_PATH = '$url' where IMG_ID = $img_id";
    ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
}

function updateProductInformation($pro_id, $pro_name_en, $pro_name_vn, $pro_code, $description_en, $description_vn, $pro_price, $cat_parent_id, $pro_status){
    $query = "update GIAAN_PRODUCT 
            set PRO_NAME_EN = '$pro_name_en', PRO_NAME_VN = '$pro_name_vn', PRO_CODE = '$pro_code', PRO_DESCRIPTION_EN = '$description_en', PRO_DESCRIPTION_VN = '$description_vn', PRO_PRICE = '$pro_price', CAT_ID = '$cat_parent_id', PRO_STATUS = '$pro_status'
            where PRO_ID = $pro_id";
    ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();        
}


function countProductOfCategory($cat_id, $keyword){
    $query = "select count(PRO_ID) as rst from GIAAN_PRODUCT where CAT_ID = $cat_id and (PRO_NAME_EN like '%$keyword%' or PRO_NAME_VN like '%$keyword%' or PRO_CODE like '%$keyword%')";
    ConnectionManager::getInstance()->setQuery($query);
	$row = mysql_fetch_array(ConnectionManager::getInstance()->query());
	return $row['rst'];
}

function countAllProduct($keyword){
    $query = "select count(PRO_ID) as rst from GIAAN_PRODUCT where PRO_NAME_EN like '%$keyword%' or PRO_NAME_VN like '%$keyword%' or PRO_CODE like '%$keyword%'";
    ConnectionManager::getInstance()->setQuery($query);
	$row = mysql_fetch_array(ConnectionManager::getInstance()->query());
	return $row['rst'];
}

function hasReceipt($pro_id){
    $query = "select count(RECEIPT_ID) as rst from GIAAN_RECEIPTITEM where PRO_ID = $pro_id";
    ConnectionManager::getInstance()->setQuery($query);
    $row = mysql_fetch_array(ConnectionManager::getInstance()->query());
	if($row['rst'] > 0){
		return true;
	}else{
		return false;	
	}
}

function findProductsBy($receiptId){
	$query = "SELECT P.PRO_CODE, P.PRO_NAME_VN, P.PRO_NAME_EN, P.PRO_STATUS, P.PRO_PRICE, 
				     (SELECT CONCAT(RI.REI_PRICE, '/', RI.REI_QUANTITY) FROM GIAAN_RECEIPTITEM RI WHERE P.PRO_ID = RI.PRO_ID AND RI.RECEIPT_ID = $receiptId) AS ITEM
			  FROM GIAAN_PRODUCT P 
			  WHERE P.PRO_ID IN (SELECT PRO_ID FROM GIAAN_RECEIPTITEM WHERE RECEIPT_ID = $receiptId) 
			  ORDER BY P.PRO_CODE";
	ConnectionManager::getInstance()->setQuery($query);
	return ConnectionManager::getInstance()->query();
}

function countAllProducts(){
	$query = "SELECT COUNT(*) AS NUM FROM GIAAN_PRODUCT";
	ConnectionManager::getInstance()->setQuery($query);
	return ConnectionManager::getInstance()->fetch(ConnectionManager::getInstance()->query());
}
?>